Imports System.Data.OleDb
Imports DTO

Public Class HocSinhDao
    Inherits AbstractDao

    Public Sub New()

    End Sub

    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From HocSinh"
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From HocSinh"
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Function LayDanhSach() As IList
        Dim ds As New ArrayList()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From HocSinh"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim hsDto As New HocSinhDto
            hsDto.Ma = dr("Ma")
            hsDto.Ten = dr("Ten")
            hsDto.DiaChi = dr("DiaChi")
            hsDto.Toan = dr("Toan")
            hsDto.Ly = dr("Ly")
            hsDto.Hoa = dr("Hoa")
            hsDto.DTB = dr("DTB")
            hsDto.MaLop = dr("MaLop")
            ds.Add(hsDto)
        End While
        'B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return ds
    End Function

    Public Function TimKiem(ByVal maHocSinh As Integer)
        Dim hsDto As New HocSinhDto
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From HocSinh Where Ma = " & maHocSinh
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            hsDto.Ma = dr("Ma")
            hsDto.Ten = dr("Ten")
            hsDto.DiaChi = dr("DiaChi")
            hsDto.Toan = dr("Toan")
            hsDto.Ly = dr("Ly")
            hsDto.Hoa = dr("Hoa")
            hsDto.DTB = dr("DTB")
            hsDto.MaLop = dr("MaLop")
        End While
        'B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return hsDto
    End Function

    Public Sub Them(ByVal hsDto As HocSinhDto)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into HocSinh(Ten, NgaySinh, DiaChi, Toan, Ly, Hoa, DTB, MaLop) values " & _
                 " ( '" & hsDto.Ten & "', " & hsDto.NgaySinh.ToShortDateString() & ", '" & hsDto.DiaChi & "', " & _
                 hsDto.Toan & ", " & hsDto.Ly & ", " & hsDto.Hoa & _
                 ", " & hsDto.DTB & ", " & hsDto.MaLop & " ) "
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, cn)
        hsDto.Ma = cmd.ExecuteScalar()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Xoa(ByVal maHocSinh As Integer)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Delete From HocSinh " & _
                 " Where Ma = " & maHocSinh
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Sua(ByVal hsDto As HocSinhDto)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Me.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Update HocSinh Set " & _
                 " Ten = '" & hsDto.Ten & "' and NgaySinh = " & hsDto.NgaySinh.ToShortDateString() & " and DiaChi = '" & _
                 hsDto.DiaChi & "' and Toan = " & hsDto.Toan & " and Ly = " & hsDto.Ly & _
                 " and Hoa = " & hsDto.Hoa & " and DTB = " & hsDto.DTB & " and MaLop = " & hsDto.MaLop & _
                 " Where Ma = " & hsDto.Ma
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub
End Class
